import json

from flask import Flask, request
from flask_cors import CORS
from pymysql import Connection

# 想要的非此种格式的sql输出 vs java mybatis的输出
# import pymysql
# pymysql.install_as_MySQLdb()
# pymysql.connections.DEBUG = True  # 这是我新加的一行
conn = Connection(
    host="192.168.91.137",
    port=3306,
    user="root",
    password="123456",
    database='layui'
)

app = Flask(__name__)

# 放开跨域的限制
cors = CORS(app, resources={r"/*": {"origins": "*"}})


@app.route('/')
def hello_world():  # put application's code here
    """
    测试输出， json对象 vs 字符串 仔细观看chrome调试模式下的输出 ps 字符串为一行
    :return:
    """
    # 对象返回
    return {"code": 0, "msg": "成功", "data": None}
    # 字符串返回
    # return json.dumps({"code": 0, "msg": "成功", "data": None} ,ensure_ascii=False)


@app.route("/listDep")
def listDep():
    """
    部门列表
    :return:
    """
    cur = conn.cursor()
    cur.execute("select dep_Id, dep_Name from dep")
    all_ret = cur.fetchall()
    list_ret = []
    for x in all_ret:
        # 提取结果： 元组形式 , 后期借助 orm框架进行快速组装
        v = dict()
        v["depId"] = x[0]
        v["depName"] = x[1]
        print("部门组装的对象数据: ", v)
        list_ret.append(v)
    print(list_ret)
    cur.close()

    obj = {"count": len(list_ret)}
    obj['data'] = list_ret
    print(obj)
    return {"code": 200, "msg": "成功", "obj": obj}
    # return json.dumps({"code": 0, "msg": "成功", "obj": obj}, ensure_ascii=False)
    pass


@app.route("/listEmp")
def listEmp():
    """
    分页获取员工列表
    :return:
    """
    pageIndex = request.args.get("pageIndex")
    pageSize = request.args.get("pageSize")

    if pageIndex is None:
        pageIndex = 0
    pageIndex = int(pageIndex) - 1  # 页面传参第一页为1
    if pageSize is None:
        pageSize = 2
    pageSize = int(pageSize)

    cur = conn.cursor()
    # 总记录数
    cur.execute("select count(1)  from emp ")
    count = cur.fetchone()

    # 分页获取
    sql = f"select emp_id, emp_name, age, hire_date, dep_id, sex  " \
          f"from emp  " \
          f"order by  emp_id  desc " \
          f"limit {pageSize} offset {pageIndex * pageSize}"
    print("分页获取员工sql:", sql)
    cur.execute(sql)
    all_ret = cur.fetchall()
    list_ret = []
    for x in all_ret:
        v = dict()
        v["empId"] = x[0]
        v["empName"] = x[1]
        v["age"] = x[2]
        v["hireDate"] = x[3].strftime('%Y-%m-%d %H:%M:%S')  # 日期获取
        v["depId"] = x[4]
        v["sex"] = x[5]
        print("员工对象:", v)

        # 分别获对应的部门， 后续可提取出来， 查一次即可， 暂先简单完成即可
        sql = f" select dep_Id, dep_Name from dep where dep_id={x[4]}"
        cur.execute(sql)
        one = cur.fetchone()
        v['dep'] = {"depId": one[0], "depName": one[1]}

        list_ret.append(v)
    print(list_ret)
    cur.close()

    obj = {"count": count[0]}
    obj['data'] = list_ret
    print(obj)
    # return json.dumps({"code": 200, "msg": "成功", "obj": obj}, ensure_ascii=False)
    return {"code": 200, "msg": "成功", "obj": obj}
    pass


@app.route("/addEmp", methods=['POST'])
def addEmp():
    """
    增加员工， post - json传参
    :return:
    """
    # // 接收json 参数
    sss = request.get_data()
    print("新增员工，获取参数(json):", sss)
    json_req: dict = json.loads(sss)
    sql = f"INSERT INTO emp ( `emp_name`, `age`, `hire_date`, `dep_id`, `sex`) " \
          f"VALUES ( '{json_req.get('empName')}', {json_req.get('age')}, '{json_req.get('hireDate')}', {json_req.get('depId')}, {json_req.get('sex')});"
    print(sql)

    cur = conn.cursor()
    cur.execute(sql)
    conn.commit()

    # return json.dumps({"code": 200, "msg": "成功", "obj": None}, ensure_ascii=False)
    return {"code": 200, "msg": "成功", "obj": None}


@app.route("/delEmp", methods=['Get'])
def delEmp():
    """
    删了员工， 根据emp_id
    :return:
    """
    # url ? 参数
    empId = request.args.get("empId")
    sql = f"delete from emp where emp_id={empId}"
    print(sql)

    cur = conn.cursor()
    cur.execute(sql)
    conn.commit()
    # return json.dumps({"code": 200, "msg": "成功", "obj": None}, ensure_ascii=False)
    return {"code": 200, "msg": "成功", "obj": None}


@app.route("/updateEmp", methods=['POST'])
def updateEmp():
    """
    更新员工
    :return:
    """
    # // 接收json 参数
    sss = request.get_data()
    json_req: dict = json.loads(sss)
    sql = f"UPDATE emp SET `emp_name` = '{json_req.get('empName')}', " \
          f"`age` = {json_req.get('age')}, " \
          f"`hire_date` = '{json_req.get('hireDate')}', " \
          f"`dep_id` = {json_req.get('depId')}, `sex` = {json_req.get('sex')} " \
          f"WHERE `emp_id` = {json_req.get('empId')} "
    print(sql)

    cur = conn.cursor()
    lines = cur.execute(sql)
    print("更新完成，受影响的结果", lines)
    conn.commit()

    return {"code": 200, "msg": "成功", "obj": None}


if __name__ == '__main__':
    app.run(debug=True, port=8080)
